package com.bw.mapper;

import com.bw.bean.Vo1;
import com.bw.bean.Vo2;
import com.bw.bean.Vo3;
import com.bw.bean.Vo4;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface Vo1Mapper {

    @Select("select ci.chapter_name name,\n" +
            "       count(ci.publisher_id) cs,\n" +
            "       sum(ucp.position_sec) sc,\n" +
            "       count(ci.chapter_name) rs,\n" +
            "       avg(ucp.position_sec) jsc\n" +
            "from user_chapter_process ucp\n" +
            "    left join chapter_info ci on ci.id = ucp.chapter_id\n" +
            "group by ci.chapter_name")
    List<Vo1> getlist();

    @Select("with a as (\n" +
            "select\n" +
            "    case\n" +
            "        when score<60 then '[0,60)'\n" +
            "        when score>=60 and score<80 then '[60,80)'\n" +
            "        when score>=80 and score<90 then '[80,90)'\n" +
            "        when score>=90 and score<95 then '[90,95)'\n" +
            "        when score>=95 then '[95,100]'\n" +
            "end name\n" +
            "from test_exam)select a.name,count(*) num from a group by a.name")
    List<Vo2> getlist2();

    @Select("select bp.name name,\n" +
            "       sum(oi.final_amount) num\n" +
            "from order_info oi\n" +
            "    left join base_province bp on bp.id = oi.province_id\n" +
            "group by oi.province_id")
    List<Vo2> getlist3();

    List<Vo2> getlist4();

    @Select("select bs.source_site name,\n" +
            "       sum(oi.final_amount) 'as',\n" +
            "       count(distinct oi.user_id) ad,\n" +
            "       count(oi.id) af\n" +
            "from order_info oi\n" +
            "    left join order_detail od on oi.id = od.order_id\n" +
            "    left join course_info ci on ci.id = od.course_id\n" +
            "    left join chapter_info c on ci.id = c.course_id\n" +
            "    left join video_info vi on c.id = vi.chapter_id\n" +
            "    left join base_source bs on bs.id = vi.video_source_id\n" +
            "group by bs.source_site")
    List<Vo3> getlist5();

    @Select("select tp.paper_title name,\n" +
            "       count(tp.id) 'as',\n" +
            "       avg(te.score) ad,\n" +
            "       avg(te.create_time) af\n" +
            "from test_exam te\n" +
            "    left join test_paper tp on tp.id = te.paper_id\n" +
            "group by tp.paper_title")
    List<Vo3> getlist6();

    @Select("select ci.course_name name,\n" +
            "       avg(ri.review_stars) cs,\n" +
            "       count(ri.course_id) sc,\n" +
            "       sum(if(ri.review_stars>3,1,0)) rs,\n" +
            "       sum(if(ri.review_stars>3,1,0))/count(ri.id) jsc\n" +
            "from review_info ri\n" +
            "    left join course_info ci on ci.id = ri.course_id\n" +
            "group by ci.course_name")
    List<Vo1> getlist7();

    @Select("select count(*) from user_info where create_time<='2021-4-23'")
    int getlist8();
}
